DROP TABLE schools;
CREATE TABLE schools (
	school_id serial primary key,
	name varchar(40) not null
);

DROP TABLE students CASCADE;
CREATE TABLE students (
	student_id serial primary key,
	school_id integer references schools(school_id) ON UPDATE CASCADE,
	first_name varchar(40) not null,
	middle_name varchar(40),
	last_name varchar(40) not null,
	ssn varchar(40),
	state_resident varchar(50) not null
);

DROP TABLE Attendant;
CREATE TABLE Attendant (
	Student_ID integer references Students(Student_ID) ON UPDATE CASCADE,
	Quarter_In varchar(40),
	Year_In varchar(40),
	Quarter_Out varchar(40),
	Year_Out varchar(40)
);

DROP TABLE Probation;
CREATE TABLE Probation (
	Probation_ID serial primary key,
	Student_ID integer references Students(Student_ID) ON UPDATE CASCADE,
	Quater_From varchar(40) not null,
	Year_From varchar(40) not null,
	Quater_To varchar(40) not null,
	Year_To varchar(40) not null,
	Reason varchar(200)
);

DROP TABLE Undergraduate;
CREATE TABLE Undergraduate (
	Student_ID integer references Students(Student_ID) ON UPDATE CASCADE
);

DROP TABLE Graduate;
CREATE TABLE Graduate (
	Student_ID integer references Students(Student_ID) ON UPDATE CASCADE
);

DROP TABLE MS;
CREATE TABLE MS (
	Student_ID integer references Students(Student_ID) ON UPDATE CASCADE
);

DROP TABLE PhD;
CREATE TABLE PhD (
	Student_ID integer references Students(Student_ID) ON UPDATE CASCADE
);

DROP TABLE Candidate;
CREATE TABLE Candidate (
	Student_ID integer references Students(Student_ID) ON UPDATE CASCADE
);

DROP TABLE Precandidate;
CREATE TABLE Precandidate (
	Student_ID integer references Students(Student_ID) ON UPDATE CASCADE
);

DROP TABLE courses CASCADE;
CREATE TABLE courses (
	course_id serial,
	department_abbreviation varchar(5) references departments(abbreviation) on update cascade not null,
	min_units integer,
	max_units integer not null check(min_units <= max_units),
	course_number varchar(10) not null,
	grade_type varchar(2) not null,
	requires_lab boolean not null,
	PRIMARY KEY(course_id)
);
CREATE UNIQUE INDEX dept_course on courses (department_abbreviation,course_number);

DROP TABLE classes CASCADE;
CREATE TABLE classes (
	class_id serial primary key,
	course_id integer not null references courses(course_id) ON UPDATE CASCADE,
	title varchar(100),
	quarter varchar(2) not null,
	year integer not null,
	enrollment_limit integer not null
);

DROP TABLE sections CASCADE;
CREATE TABLE sections (
	section_id serial primary key,
	class_id integer not null references classes(class_id) ON UPDATE CASCADE
);

DROP TABLE meetings;
CREATE TABLE meetings (
	meeting_id serial primary key,
	section_id integer not null references sections(section_id) ON UPDATE CASCADE,
	meeting_type varchar(20) not null,
	meeting_day varchar(10) not null,
	meeting_start_time time not null,
	meeting_end_time time not null,
	required boolean default false not null
);

DROP TABLE Enroll;
CREATE TABLE Enroll (
	Student_ID integer references Students(Student_ID) ON UPDATE CASCADE,
	Section_ID integer references Sections(Section_ID) ON UPDATE CASCADE,
	Waitlist_Number integer,
	Grade_Letter varchar(10),
	units integer not null
);

DROP TABLE Thesis CASCADE;
CREATE TABLE Thesis (
	Thesis_ID serial primary key,
	Name varchar(40) not null
);

DROP TABLE Works_On;
CREATE TABLE Works_On (
	Student_ID integer references Students(Student_ID) ON UPDATE CASCADE,
	Thesis_ID integer references Thesis(Thesis_ID) ON UPDATE CASCADE
);

DROP TABLE Degrees;
CREATE TABLE Degrees (
	Degrees_ID serial primary key,
	Student_ID integer references Students(Student_ID) ON UPDATE CASCADE,
	Name varchar(100) not null
);

DROP TABLE departments CASCADE;
CREATE TABLE departments (
	department_id serial primary key,
	abbreviation varchar(5) unique not null,
	name varchar(40) not null
);

DROP TABLE Major_In;
CREATE TABLE Major_In (
	Student_ID integer references Students(Student_ID) ON UPDATE CASCADE,
	Department_ID integer references Departments(Department_ID) ON UPDATE CASCADE
);

DROP TABLE Minor_In;
CREATE TABLE Minor_In (
	Student_ID integer references Students(Student_ID) ON UPDATE CASCADE,
	Department_ID integer references Departments(Department_ID) ON UPDATE CASCADE
);

DROP TABLE Degrees_Offered;
CREATE TABLE Degrees_Offered (
	Degrees_ID serial primary key,
	Department_ID integer references Departments(Department_ID),
	Name varchar(200),
	Type_Degree varchar(40),
	Lower_Units_Reqd integer not null,
	Upper_Units_Reqd integer not null,
	elective_units_reqd integer
);

DROP TABLE Prerequisite_Of;
CREATE TABLE Prerequisite_Of (
	High_Course_ID integer references Courses(Course_ID) ON UPDATE CASCADE,
	Low_Course_ID integer references Courses(Course_ID) ON UPDATE CASCADE
);

DROP TABLE Weekly_Sections;
CREATE TABLE Weekly_Sections (
	Section_ID integer references classes(class_id) ON UPDATE CASCADE,
	Type varchar(40),
	Days timestamp,
	Time timestamp,
	Building_Room varchar(40) not null,
	Mandatory boolean NOT NULL DEFAULT false
);

DROP TABLE Review_Sections;
CREATE TABLE Review_Sections (
id serial primary key,
	Section_ID integer references classes(class_id) ON UPDATE CASCADE,
	Date  varchar(40) not null,
	Time  varchar(40) not null,
	Building_Room varchar(40) not null,
	Mandatory boolean NOT NULL DEFAULT false
);

DROP TABLE faculty;
CREATE TABLE faculty (
	faculty_id serial primary key,
	department_id integer references departments(department_id) ON UPDATE CASCADE,
	first_name varchar(40) not null,
	middle_name varchar(40),
	last_name varchar(40) not null,
	title varchar(40) not null	
);

DROP TABLE Teaches;
CREATE TABLE Teaches (
	Faculty_ID integer references Faculty(Faculty_ID) ON UPDATE CASCADE,
	Section_ID integer references Sections(section_id) ON UPDATE CASCADE	
);

DROP TABLE Advisor;
CREATE TABLE Advisor (
	Faculty_ID integer references Faculty(Faculty_ID) ON UPDATE CASCADE,
	Student_ID integer references Students(Student_ID) ON UPDATE CASCADE
);

DROP TABLE Committee;
CREATE TABLE Committee (
	Faculty_ID integer references Faculty(Faculty_ID) ON UPDATE CASCADE,
	Thesis_ID integer references Thesis(Thesis_ID)  ON UPDATE CASCADE
);

DROP TABLE GRADE_CONVERSION;
create table GRADE_CONVERSION
( LETTER_GRADE CHAR(2) NOT NULL,
NUMBER_GRADE DECIMAL(2,1)
);

DROP TABLE Category CASCADE;
CREATE TABLE Category (
	Category_ID serial primary key,
	name varchar(100) not null,
	minimunGPA DECIMAL(2,1),
	min_units integer
);

DROP TABLE Course_Category;
CREATE TABLE Course_Category (
	Category_ID integer references Category(Category_ID) ON UPDATE CASCADE,
	Course_ID integer references Courses(Course_ID)	ON UPDATE CASCADE
);

DROP TABLE potential_review_session_slots;
CREATE TABLE potential_review_session_slots (
	slot_id serial primary key,
	day varchar(10) not null,
	start_time time without time zone not null,
	end_time time without time zone
);

DROP TABLE temp_date_intervals;
CREATE TABLE temp_date_intervals (
	interval_day VARCHAR(10),
	interval_date DATE
);

DROP FUNCTION make_date_interval(date,date);
CREATE FUNCTION make_date_interval(startdate DATE, enddate DATE) RETURNS TABLE(interval_day VARCHAR(10), interval_date DATE) AS $$
DECLARE thisDate DATE;
DECLARE currentDayInt INTEGER;
DECLARE currentDay VARCHAR(10);
BEGIN
	SELECT startdate INTO thisDate;
	TRUNCATE TABLE temp_date_intervals;
	LOOP
		SELECT EXTRACT(DOW FROM thisDate) INTO currentDayInt;
		IF currentDayInt = 0 THEN
			SELECT 'Sunday' INTO currentDay;
		ELSIF currentDayInt = 1 THEN
			SELECT 'Monday' INTO currentDay;
		ELSIF currentDayInt = 2 THEN
			SELECT 'Tueday' INTO currentDay;
		ELSIF currentDayInt = 3 THEN
			SELECT 'Wednesday' INTO currentDay;
		ELSIF currentDayInt = 4 THEN
			SELECT 'Thursday' INTO currentDay;
		ELSIF currentDayInt = 5 THEN
			SELECT 'Friday' INTO currentDay;
		ELSIF currentDayInt = 6 THEN
			SELECT 'Saturday' INTO currentDay;
		END IF;
		INSERT INTO temp_date_intervals VALUES ((SELECT currentDay),(SELECT thisDate));
		SELECT thisDate + 1 INTO thisDate;
		EXIT WHEN thisDate > enddate;
	END LOOP;
	RETURN QUERY(SELECT t.interval_day, t.interval_date FROM temp_date_intervals t);
END; 
$$ LANGUAGE PLPGSQL;

DROP TRIGGER check_schedule IF EXISTS ON meetings;
DROP FUNCTION IF EXISTS check_professor_schedule() CASCADE;

CREATE FUNCTION check_professor_schedule() RETURNS trigger AS $$
DECLARE faculty INT;
BEGIN
	SELECT faculty_id FROM teaches WHERE section_id = NEW.section_id INTO faculty;
	IF ( EXISTS( SELECT * FROM teaches t LEFT JOIN meetings m ON (t.section_id = m.section_id) WHERE t.section_id != NEW.section_id AND t.faculty_id = (SELECT faculty) AND m.meeting_day = NEW.meeting_day AND ((NEW.meeting_start_time >= m.meeting_start_time AND NEW.meeting_start_time < m.meeting_end_time) OR (NEW.meeting_end_time > m.meeting_start_time AND NEW.meeting_end_time <= m.meeting_end_time)))) THEN
		RAISE EXCEPTION 'A faculty member cannot teach two sections at the same time.';
	END IF;
	RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER check_schedule BEFORE INSERT OR UPDATE ON meetings
	FOR EACH ROW EXECUTE PROCEDURE check_professor_schedule();

DROP TRIGGER check_meetings IF EXISTS ON meetings;
DROP FUNCTION IF EXISTS check_meeting_times() CASCADE;

CREATE FUNCTION check_meeting_times() RETURNS trigger AS $$
BEGIN
	IF ( EXISTS( SELECT * FROM meetings m WHERE m.section_id = NEW.section_id AND m.meeting_day = NEW.meeting_day AND ( (NEW.meeting_start_time >= m.meeting_start_time AND NEW.meeting_start_time < m.meeting_end_time) OR (NEW.meeting_end_time > m.meeting_start_time AND NEW.meeting_end_time <= m.meeting_end_time) ) ) ) THEN
		RAISE EXCEPTION 'You cannot have a meeting at the same time as another meeting.';
	END IF;
	RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER check_meetings BEFORE INSERT OR UPDATE ON meetings
	FOR EACH ROW EXECUTE PROCEDURE check_meeting_times();


DROP FUNCTION IF EXISTS check_class_size() CASCADE;
DROP TRIGGER IF EXISTS check_enrollment ON enroll;

CREATE FUNCTION check_class_size() RETURNS trigger AS $$
BEGIN
	IF (  ( SELECT count(*) FROM enroll WHERE section_id = NEW.section_id ) = (SELECT enrollment_limit FROM sections LEFT JOIN classes ON (sections.class_id = classes.class_id) WHERE section_id = NEW.section_id ) ) THEN
		RAISE EXCEPTION 'Class is already full';
	END IF;
	RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER check_enrollment BEFORE INSERT ON enroll
	FOR EACH ROW EXECUTE PROCEDURE check_class_size();
	


INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Monday','08:00','09:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Monday','09:00','10:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Monday','10:00','11:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Monday','11:00','12:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Monday','12:00','13:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Monday','13:00','14:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Monday','14:00','15:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Monday','15:00','16:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Monday','16:00','17:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Monday','17:00','18:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Monday','18:00','19:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Monday','19:00','20:00');

INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Tuesday','08:00','09:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Tuesday','09:00','10:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Tuesday','10:00','11:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Tuesday','11:00','12:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Tuesday','12:00','13:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Tuesday','13:00','14:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Tuesday','14:00','15:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Tuesday','15:00','16:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Tuesday','16:00','17:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Tuesday','17:00','18:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Tuesday','18:00','19:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Tuesday','19:00','20:00');

INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Wednesday','08:00','09:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Wednesday','09:00','10:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Wednesday','10:00','11:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Wednesday','11:00','12:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Wednesday','12:00','13:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Wednesday','13:00','14:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Wednesday','14:00','15:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Wednesday','15:00','16:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Wednesday','16:00','17:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Wednesday','17:00','18:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Wednesday','18:00','19:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Wednesday','19:00','20:00');

INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Thursday','08:00','09:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Thursday','09:00','10:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Thursday','10:00','11:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Thursday','11:00','12:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Thursday','12:00','13:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Thursday','13:00','14:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Thursday','14:00','15:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Thursday','15:00','16:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Thursday','16:00','17:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Thursday','17:00','18:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Thursday','18:00','19:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Thursday','19:00','20:00');

INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Friday','08:00','09:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Friday','09:00','10:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Friday','10:00','11:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Friday','11:00','12:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Friday','12:00','13:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Friday','13:00','14:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Friday','14:00','15:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Friday','15:00','16:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Friday','16:00','17:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Friday','17:00','18:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Friday','18:00','19:00');
INSERT INTO potential_review_session_slots (day,start_time,end_time) VALUES ('Friday','19:00','20:00');

insert into grade_conversion values('A+', 4.3);
insert into grade_conversion values('A', 4);
insert into grade_conversion values('A-', 3.7);
insert into grade_conversion values('B+', 3.4);
insert into grade_conversion values('B', 3.1);
insert into grade_conversion values('B-', 2.8);
insert into grade_conversion values('C+', 2.5);
insert into grade_conversion values('C', 2.2);
insert into grade_conversion values('C-', 1.9);
insert into grade_conversion values('D', 1.6); 



INSERT INTO schools (name) VALUES('Revelle College');
INSERT INTO schools (name) VALUES('John Muir College');
INSERT INTO schools (name) VALUES('Thurgood Marshall College');
INSERT INTO schools (name) VALUES('Earl Warren College');
INSERT INTO schools (name) VALUES('Eleanor Roosevelt College');
INSERT INTO schools (name) VALUES('Sixth College');


INSERT INTO departments(name,abbreviation) VALUES ('Anthropology','ANTH');
INSERT INTO departments(name,abbreviation) VALUES ('Computer Science and Engineering', 'CSE');
INSERT INTO departments(name,abbreviation) VALUES ('Cognitive Science', 'COGS');
INSERT INTO departments(name,abbreviation) VALUES ('Communication', 'COMM');
INSERT INTO departments(name,abbreviation) VALUES ('Electrical and Computer Engineering', 'ECE');
INSERT INTO departments(name,abbreviation) VALUES ('Mathematics', 'MATH');

INSERT INTO Category (name, minimunGPA, min_units) VALUES('Lower Division', 3.0,0);
INSERT INTO Category (name, minimunGPA, min_units) VALUES('Upper Division', 2.0,0);
INSERT INTO Category (name, minimunGPA, min_units) VALUES('Technical Elective', 3.0,0);
INSERT INTO Category (name, minimunGPA, min_units) VALUES('MS Database', 3.0,6);
INSERT INTO Category (name, minimunGPA, min_units) VALUES('MS Network', 3.0,6);

